package cn.superfw.demo.querydsl.controller;

import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQueryFactory;

import cn.superfw.demo.querydsl.domain.IDCard;
import cn.superfw.demo.querydsl.domain.Person;
import cn.superfw.demo.querydsl.domain.QIDCard;
import cn.superfw.demo.querydsl.domain.QPerson;
import cn.superfw.demo.querydsl.model.PersonInfoModel;
import cn.superfw.demo.querydsl.repository.IDCardRepository;
import cn.superfw.demo.querydsl.repository.PersonRepository;

@Controller
public class TestController {

	@PersistenceContext
    EntityManager em;

	@Autowired
	PersonRepository personRepository;

	@Autowired
	IDCardRepository idCardRepository;

	@RequestMapping(value="/init")
	@Transactional
	public @ResponseBody String init() {
		Person p = new Person();
		p.setName("张三");
		p.setSex(1);
		p.setBirthday(new Date());
		personRepository.save(p);

		IDCard idCard = new IDCard();
		idCard.setPersonId(p.getId());
		idCard.setIdCard("120111201406060150");
		idCardRepository.save(idCard);

		return "OK";
	}

	/**
	 * 两个表外键连接查询，查询结果组装到Bean
	 * @return List<PersonInfoModel>
	 *
	 */
	@RequestMapping(value="/testOneToOneJoin")
	public @ResponseBody List<PersonInfoModel> testOneToOneJoin() {

		JPAQueryFactory queryFactory = new JPAQueryFactory(em);
		QPerson person = QPerson.person;
		QIDCard idCard = QIDCard.iDCard;

		List<PersonInfoModel> list = queryFactory
				.select(Projections.bean(PersonInfoModel.class,
						person.name,
						person.sex.as("sex"), // 别名指定，仅仅是演示
						idCard.idCard))
				.from(person, idCard)
				.where(
						person.id.eq(idCard.personId), // 连接条件
						person.name.like("张%")) // 查询条件
				.orderBy(person.sex.desc()) // 排序
				.offset(0)
				.limit(10)
				.fetch();

		/* 生成的SQL
			SELECT
			  person0_.name AS col_0_0_,
			  person0_.sex AS col_1_0_,
			  idcard1_.id_card AS col_2_0_
			FROM
			  t_person person0_
			  CROSS JOIN t_id_card idcard1_
			WHERE person0_.id = idcard1_.person_id
			  AND (person0_.name LIKE ? ESCAPE '!')
			ORDER BY person0_.sex DESC
			LIMIT ?
		*/

		return list;
	}

	/**
	 * 两个表外键连接查询，查询结果利用Java8特性组装到Bean
	 * @return List<PersonInfoModel>
	 *
	 */
	@RequestMapping(value="/testOneToOneJoinJava8")
	public @ResponseBody List<PersonInfoModel> testOneToOneJoinJava8() {
		JPAQueryFactory queryFactory = new JPAQueryFactory(em);
		QPerson person = QPerson.person;
		QIDCard idCard = QIDCard.iDCard;

		List<PersonInfoModel> list = queryFactory
				.select(
						person.name,
						person.sex.as("sex"), // 别名指定，仅仅是演示
						idCard.idCard)
				.from(person, idCard)
				.where(
						person.id.eq(idCard.personId), // 连接条件
						person.name.like("张%")) // 查询条件
				.orderBy(person.sex.desc()) // 排序
				.offset(0)
				.limit(10)
				.fetch()
				.stream()
				//转换集合内的数据
				.map(tuple -> {
					// 创建结果集封装对象并赋值
					PersonInfoModel dto = new PersonInfoModel();
					dto.setName(tuple.get(person.name));
					dto.setSex(tuple.get(person.sex));
					dto.setIdCard(tuple.get(idCard.idCard));
					return dto;
				})
				//返回集合并且转换为List<PersonInfoModel>
				.collect(Collectors.toList());
		return list;
	}

}
